﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data;
using System.Data.SQLite;
namespace mysix
{
    internal class StuDB
    {
        private SQLiteConnection connection;
        string connectionString = "Data Source=mydatabase.db;Version=3;";

        public StuDB()
        {
            // 在构造函数中初始化数据库连接
            connection = new SQLiteConnection(connectionString);
        }

        public DataTable GetStudentData()
        {
            // 创建一个 DataTable 以获取 Student 表的数据
            DataTable studentDataTable = new DataTable();

            try
            {
                connection.Open();

                string selectStudentSQL = "SELECT SchoolId, ClassId, StuId, StuName FROM Student";
                SQLiteCommand selectStudentCmd = new SQLiteCommand(selectStudentSQL, connection);

                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(selectStudentCmd))
                {
                    adapter.Fill(studentDataTable);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }

            return studentDataTable;
        }

        public void AddStudent(int schoolId, int classId, int stuId, string stuName)
        {
            try
            {
                connection.Open();

                string insertStudentSQL = "INSERT INTO Student (SchoolId, ClassId, StuId, StuName) VALUES (@SchoolId, @ClassId, @StuId, @StuName)";
                SQLiteCommand insertStudentCmd = new SQLiteCommand(insertStudentSQL, connection);
                insertStudentCmd.Parameters.AddWithValue("@SchoolId", schoolId);
                insertStudentCmd.Parameters.AddWithValue("@ClassId", classId);
                insertStudentCmd.Parameters.AddWithValue("@StuId", stuId);
                insertStudentCmd.Parameters.AddWithValue("@StuName", stuName);
                insertStudentCmd.ExecuteNonQuery();

                Console.WriteLine("Student added successfully.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        public void DeleteStudent(int schoolId, int classId, int stuId)
        {
            try
            {
                connection.Open();

                string deleteStudentSQL = "DELETE FROM Student WHERE SchoolId = @SchoolId AND ClassId = @ClassId AND StuId = @StuId";
                SQLiteCommand deleteStudentCmd = new SQLiteCommand(deleteStudentSQL, connection);
                deleteStudentCmd.Parameters.AddWithValue("@SchoolId", schoolId);
                deleteStudentCmd.Parameters.AddWithValue("@ClassId", classId);
                deleteStudentCmd.Parameters.AddWithValue("@StuId", stuId);
                deleteStudentCmd.ExecuteNonQuery();

                Console.WriteLine("Student deleted successfully.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        public void UpdateStudent(int schoolId, int classId, int stuId, string stuName)
        {
            try
            {
                connection.Open();

                string updateStudentSQL = "UPDATE Student SET StuName = @StuName WHERE SchoolId = @SchoolId AND ClassId = @ClassId AND StuId = @StuId";
                SQLiteCommand updateStudentCmd = new SQLiteCommand(updateStudentSQL, connection);
                updateStudentCmd.Parameters.AddWithValue("@StuName", stuName);
                updateStudentCmd.Parameters.AddWithValue("@SchoolId", schoolId);
                updateStudentCmd.Parameters.AddWithValue("@ClassId", classId);
                updateStudentCmd.Parameters.AddWithValue("@StuId", stuId);
                updateStudentCmd.ExecuteNonQuery();

                Console.WriteLine("Student updated successfully.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }
        public DataTable Search1Student(int schoolId, int classId, int stuId)
        {
            DataTable resultTable = new DataTable();

            try
            {
                connection.Open();

                string selectStudentSQL = "SELECT SchoolId, ClassId, StuId, StuName FROM Student WHERE SchoolId = @SchoolId AND ClassId = @ClassId AND StuId = @StuId";
                SQLiteCommand selectStudentCmd = new SQLiteCommand(selectStudentSQL, connection);
                selectStudentCmd.Parameters.AddWithValue("@SchoolId", schoolId);
                selectStudentCmd.Parameters.AddWithValue("@ClassId", classId);
                selectStudentCmd.Parameters.AddWithValue("@StuId", stuId);

                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(selectStudentCmd))
                {
                    adapter.Fill(resultTable);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }

            return resultTable;
        }

        public void SearchStudent(int schoolId, int classId, int stuId)
        {
            try
            {
                connection.Open();

                string selectStudentSQL = "SELECT SchoolId, ClassId, StuId, StuName FROM Student WHERE SchoolId = @SchoolId AND ClassId = @ClassId AND StuId = @StuId";
                SQLiteCommand selectStudentCmd = new SQLiteCommand(selectStudentSQL, connection);
                selectStudentCmd.Parameters.AddWithValue("@SchoolId", schoolId);
                selectStudentCmd.Parameters.AddWithValue("@ClassId", classId);
                selectStudentCmd.Parameters.AddWithValue("@StuId", stuId);

                using (SQLiteDataReader reader = selectStudentCmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int schoolIdValue = reader.GetInt32(0);
                        int classIdValue = reader.GetInt32(1);
                        int stuIdValue = reader.GetInt32(2);
                        string stuName = reader.GetString(3);
                        Console.WriteLine($"SchoolId: {schoolIdValue}, ClassId: {classIdValue}, StuId: {stuIdValue}, StuName: {stuName}");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }
    }

}
